﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace SisLegal.Model
{
    
    public class Conciliation
    {
        public int ID { get; set; }
        public string NroConciliation { get; set; }
        public string City { get; set; }
        public string Solicitante { get; set; }
        public string Invitado { get; set; }
        public int ParticipationSeal { get; set; }
        public DateTime DateInvitation { get; set; }      
	    public DateTime FirstCitation { get; set; }
        public DateTime SecondCitation { get; set; }
        public string Sumilla { get; set; }
        public string Observaciones { get; set; }


        public Conciliation()
        {
            this.ID = 0;
            this.NroConciliation = "";
            this.City = "";
            this.Solicitante = "";
            this.Invitado = "";
            this.ParticipationSeal = 0;
            this.DateInvitation = new DateTime();
            this.FirstCitation = new DateTime();
            this.SecondCitation = new DateTime();
            this.Sumilla = "";
            this.Observaciones = "";
        }
    }

    public class ConciliationManager
    {
        public List<Conciliation> GetAll()
        {
            List<Conciliation> result = new List<Conciliation>();
            string sqlQuery = String.Format("SELECT * FROM Conciliation ORDER BY ID");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            Conciliation process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new Conciliation();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroConciliation = dataReader["NroFiscalFolder"].ToString();
                    process.City = dataReader["City"].ToString();
                    process.Solicitante = dataReader["Solicitante"].ToString();
                    process.Invitado = dataReader["Invitado"].ToString();
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.DateInvitation = Convert.ToDateTime(dataReader["DateInvitation"]);
                    process.FirstCitation = Convert.ToDateTime(dataReader["FirstCitation"]);
                    process.SecondCitation = Convert.ToDateTime(dataReader["SecondCitation"]);
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    result.Add(process);

                }
            }
            return result;
        }
        public Int32 Count()
        {            
            string sqlQuery = String.Format("  SELECT COUNT(ID) AS total FROM Conciliation");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            command.CommandType = CommandType.Text;
            Int32 countStart = (Int32)command.ExecuteScalar();
            return countStart;
        }

        public List<Conciliation> Paginate(Int32 pag)
        {
            List<Conciliation> result = new List<Conciliation>();
            
            string sqlQuery = String.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, * FROM Conciliation e) as tbl " +
            "WHERE NUMBER BETWEEN((" + Convert.ToString(pag) + "-1) * (10 +1)) AND (" + Convert.ToString(pag) + " * 10)" +
            "ORDER BY tbl.ID ");

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            Conciliation process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new Conciliation();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroConciliation = dataReader["NroConciliation"].ToString();
                    process.City = dataReader["City"].ToString();
                    process.Solicitante = dataReader["Solicitante"].ToString();
                    process.Invitado = dataReader["Invitado"].ToString();
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.DateInvitation = Convert.ToDateTime(dataReader["DateInvitation"]);
                    process.FirstCitation = Convert.ToDateTime(dataReader["FirstCitation"]);
                    process.SecondCitation = Convert.ToDateTime(dataReader["SecondCitation"]);
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    result.Add(process);
                }
            }
            return result;
        }

        public int Save(Conciliation obj)
        {
            string createQuery = String.Format("Insert into Conciliation (NroConciliation "+
           ",City, Solicitante, Invitado, ParticipationSeal, DateInvitation,  FirstCitation" +
           ",SecondCitation, Sumilla, Observaciones) " +
           " Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');"
                + "Select @@Identity", obj.NroConciliation,
                obj.City, obj.Solicitante, obj.Invitado, obj.ParticipationSeal, obj.DateInvitation, obj.FirstCitation,
                obj.SecondCitation, obj.Sumilla, obj.Observaciones);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;

            command = new SqlCommand(createQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
            }
            catch (Exception ex)
            {

            }

            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }

        public Conciliation GetById(int articleId)
        {


            Conciliation process = new Conciliation();
            string sqlQuery = String.Format("select * from Conciliation where ID={0}", articleId);
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {                    
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroConciliation = dataReader["NroConciliation"].ToString();
                    process.City = dataReader["City"].ToString();
                    process.Solicitante = dataReader["Solicitante"].ToString();
                    process.Invitado = dataReader["Invitado"].ToString();
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.DateInvitation = Convert.ToDateTime(dataReader["DateInvitation"]);
                    process.FirstCitation = Convert.ToDateTime(dataReader["FirstCitation"]);
                    process.SecondCitation = Convert.ToDateTime(dataReader["SecondCitation"]);
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                }
            }

            return process;
        }

        public int Update(Conciliation obj)
        {
            string updateQuery = String.Format("UPDATE Conciliation SET NroConciliation = '{0}'" +
           ",City ='{1}', Solicitante ='{2}', Invitado ='{3}', ParticipationSeal ='{4}', DateInvitation ='{5}', FirstCitation = '{6}'" +
           ",SecondCitation ='{7}', Sumilla ='{8}', Observaciones ='{9}' WHERE ID = {10} ",
            obj.NroConciliation, obj.City, obj.Solicitante, obj.Invitado, obj.ParticipationSeal, obj.DateInvitation, obj.FirstCitation,
                obj.SecondCitation, obj.Sumilla, obj.Observaciones, obj.ID);           

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;
            command = new SqlCommand(updateQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
                else
                {
                    saveID = obj.ID;
                }
            }
            catch (Exception ex)
            {

            }
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }
        
        

        public bool Delete(int articleId)
        {
            bool result = false;
            string sqlQuery = String.Format("delete from Conciliation where ID = {0}", articleId);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            int rowsDeletedCount = command.ExecuteNonQuery();
            if (rowsDeletedCount != 0)
                result = true;
            command.Dispose();
            connection.Close();
            connection.Dispose();
            return result;
        }
    }
}